package com.easylinkin.linkappapi.common.utils.excel;

import com.easylinkin.linkappapi.common.exceptions.BusinessException;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.NumberToTextConverter;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Component;
import org.springframework.web.multipart.MultipartFile;

/**
 * 1.读取excel,解析Excel表格中数据内容 2.校验表格标题的格式是否符合模板
 *
 * @author tongjie
 * @date 2020-02-12
 */
@Component
public class ExcelReadUtil<T> {

    public static final String EXTENSION_XLS = "xls";
    public static final Logger LOGGER = LoggerFactory.getLogger(ExcelReadUtil.class);


    /**
     * 构造方法
     */
    public ExcelReadUtil() {
    }

    /**
     * 读EXCEL文件，获取区域信息集合
     *
     * @param startRow 开始读取的行数
     * @param size 要读取多少行
     * @param readCells 要读取多少列，传-1 则是读取所有列
     * @return 读取后解析得到的表格内容
     */
    public static List<List<String>> getExcelInfo(MultipartFile multipartFile, int startRow,
        Integer size, int readCells)

    {
        //初始化输入流
        InputStream inputStream;
        try {
            inputStream = multipartFile.getInputStream();
        } catch (IOException e) {
            LOGGER.error(e.getMessage(), e);
            throw new BusinessException("读取文件获取输入流失败。");
        }

        //根据文件名判断文件是2003版本还是2007版本
        boolean isXls = false;
        //获取文件名
        String fileName = multipartFile.getOriginalFilename();
        if (isXls(fileName)) {
            isXls = true;
        }

        return getExcelInfo(inputStream, startRow, size, isXls, readCells);
    }

    /**
     * * 读EXCEL文件，获取区域信息集合
     *
     * @param startRow 从哪一行开始读取内容
     * @param size 需要读多少条
     * @param readCells 需要读多少列。 -1 则是读取所有列
     * @param isXls 2003版为true，如果是xlsx为false
     * @return 读取到的内容
     */
    public static List<List<String>> getExcelInfo(InputStream inputStream, int startRow,
        Integer size, boolean isXls, int readCells)

    {
        //初始化区域信息的集合
        List<List<String>> excelList = new ArrayList<>();
        //初始化输入流

        try {
            //根据新建的文件实例化输入流
            //根据excel里面的内容读取区域信息
            Workbook wb;

            if (isXls) {
                //当excel是xls时
                wb = new HSSFWorkbook(inputStream);
            } else { //当excel是xlsx时
                wb = new XSSFWorkbook(inputStream);
            }
            //读取Excel里面区域的信息
            excelList = readExcelValue(wb, startRow, size, readCells);

            inputStream.close();
        } catch (IOException e) {
            LOGGER.error(e.getMessage(), e);
        } finally {
            if (inputStream != null) {
                try {
                    inputStream.close();
                } catch (Exception e) {
                    LOGGER.error(e.getMessage(), e);
                }
            }
        }
        return excelList;
    }

    /**
     * 验证导入文件 是否使用了正确的模板，主要验证表格头,以及表格第N列是否缺失或多了字段
     */
    public static void validateExeclTemplet(List<List<String>> contentList, int indexRow,
        Map<Integer, String> standardRow) {

        if (null == contentList || contentList.size() < indexRow
            || null == contentList.get(indexRow)) {
            throw new BusinessException("表格模板错误，请检查表格的正确性");
        }

        List<String> titleRow = contentList.get(indexRow);

        boolean invalid = false;
        String msg = "";
        for (Map.Entry<Integer, String> e : standardRow.entrySet()) {
            if (titleRow.size() < e.getKey() + 1
                || e.getValue() == null || !e.getValue().equals(titleRow.get(e.getKey()))) {
                invalid = true;
                msg = titleRow.get(e.getKey());
                break;
            }
        }

        if (invalid) {
            throw new BusinessException("表格模板错误，请检查是否使用了最新的正确模板,请检查表头：" + msg);
        }
    }

    /**
     * 读取Excel里面区域的信息
     *
     * @param startRow 起始行，上面的行忽略掉
     * @param size 需要读多少条，如果传null, 则读下面所有
     * @param readCells 需要读取列数，如果传-1 则自动从表格读取所有列
     * @return List<List < String>> 二维集合，装表格所有内容的字符串格式
     */
    private static List<List<String>> readExcelValue(Workbook wb, int startRow,
        Integer size, int readCells) {

        List<List<String>> excelList = new ArrayList<>();

        //得到第一个shell
        Sheet sheet = wb.getSheetAt(0);

        //得到Excel的行数
        int totalRows = sheet.getPhysicalNumberOfRows();

        //如果传参读取表格数readCell不大于0，则读取表格获取Excel的列数(前提是有行数)
        if (readCells <= 0) {
            if (totalRows >= 1 && sheet.getRow(startRow) != null) {
                //update kanyuanfeng 如果有标题。应该读取标题的列数
                if (startRow>0){
                    readCells = sheet.getRow(startRow-1).getPhysicalNumberOfCells();
                }else {
                    readCells = sheet.getRow(startRow).getPhysicalNumberOfCells();
                }

            }
        }

        if (readCells <= 0) {
            return excelList;
        }

        if (size == null || size.equals(-1)) {
            size = totalRows;
        }

        //循环Excel行数,从第三行开始。标题不入库
        for (int r = startRow; r < startRow + size; r++) {
            Row row = sheet.getRow(r);
            if (row == null) {
                continue;
            }
            List<String> rowContent = new ArrayList<>();
            //循环Excel的列
            for (int c = 0; c < readCells; c++) {
                Cell cell = row.getCell(c);
                if (isMergedRegion(sheet, r, c)) {
                    rowContent.add(getMergedRegionValue(sheet, r, c));
                } else {
                    rowContent.add(getStrValueFromCell(cell));
                }

            }
            //添加区域
            excelList.add(rowContent);
        }
        return excelList;
    }


    /**
     * 如果是字符串直接取值 如果是数值需要做转换
     */
    private static String getStrValueFromCell(Cell cell) {
        if (null == cell) {
            return "";
        }

        if (cell.getCellType() == CellType.BOOLEAN) {
            return String.valueOf(cell.getBooleanCellValue());
        } else if (cell.getCellType() == CellType.NUMERIC) {
            //数值类型又具体区分日期类型，单独处理
            if (HSSFDateUtil.isCellDateFormatted(cell)) {
                Date date = cell.getDateCellValue();
                SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
                return simpleDateFormat.format(date);
            } else {
                return NumberToTextConverter.toText(cell.getNumericCellValue());
            }
        } else {
            return String.valueOf(cell.getStringCellValue());
        }
    }

    /**
     * 判断是否是xls 2003,还是2007 xlsx
     */
    public static boolean isXls(String fileName) {
        //获得文件的扩展名
        String extension = fileName.substring(fileName.lastIndexOf(".") + 1);
        return EXTENSION_XLS.equalsIgnoreCase(extension);

    }

    /**
     * 校验表格中字段必填
     */
    public static int validateRequiredFields(List<List<String>> contentList,
        int[] requiredColumnNums) {

        for (int row = 0; row < contentList.size(); row++) {
            for (int i : requiredColumnNums) {
                if (null == contentList.get(row).get(i)
                    || "".equals(contentList.get(row).get(i))) {
                    return row + 1;
                }
            }
        }
        return -1;
    }

    /**
     * 获取合并单元格的值
     *
     * @param row 行下标
     * @param column 列下标
     */
    public static String getMergedRegionValue(Sheet sheet, int row, int column) {
        int sheetMergeCount = sheet.getNumMergedRegions();

        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress ca = sheet.getMergedRegion(i);
            int firstColumn = ca.getFirstColumn();
            int lastColumn = ca.getLastColumn();
            int firstRow = ca.getFirstRow();
            int lastRow = ca.getLastRow();

            if (row >= firstRow && row <= lastRow) {
                if (column >= firstColumn && column <= lastColumn) {
                    Row fRow = sheet.getRow(firstRow);
                    Cell fCell = fRow.getCell(firstColumn);
                    return getStrValueFromCell(fCell);
                }
            }
        }

        return null;
    }

    /**
     * 判断指定的单元格是否是合并单元格
     *
     * @param row 行下标
     * @param column 列下标
     */
    private static boolean isMergedRegion(Sheet sheet, int row, int column) {
        int sheetMergeCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress range = sheet.getMergedRegion(i);
            int firstColumn = range.getFirstColumn();
            int lastColumn = range.getLastColumn();
            int firstRow = range.getFirstRow();
            int lastRow = range.getLastRow();
            if (row >= firstRow && row <= lastRow) {
                if (column >= firstColumn && column <= lastColumn) {
                    return true;
                }
            }
        }
        return false;
    }

}

